﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
/*********************************
 * 类名：DBHelper
 * 功能描述：提供基本数据访问功能
 * ******************************/
namespace DbHelper
{
    public static class DBHelper
    {
        //数据库连接属性
        private static SQLiteConnection connection;
        public static SQLiteConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.AppSettings.GetValues("ConnectString")[0];
                if (connection == null)
                {
                    connection = new SQLiteConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Dispose();
                    connection.Open();
                }
                return connection;
            }
        }
        /// <summary>
        /// 执行带参存储过程
        /// </summary>
        public static void ExecProc(string strProName, SQLiteParameter[] para)
        {
            //connection.Open();
            try
            {
                SQLiteCommand sqlcmd = connection.CreateCommand();
                sqlcmd.CommandText = strProName;
                sqlcmd.CommandType = CommandType.StoredProcedure;
                foreach (SQLiteParameter paras in para)
                {
                    sqlcmd.Parameters.Add(paras);
                }
                sqlcmd.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 根据sql语句执行非查询操作
        /// </summary>
        public static bool ExecNoQuery(string sql)
        {
            //connection.Open();
            try
            {
                SQLiteCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                return true;
            }
            catch
            {
                return false;
            }
            //Connection.Close();
        }
        /// <summary>
        /// 执行无参SQL语句
        /// </summary>
        public static int ExecuteCommand(string safeSql)
        {
            SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 执行带参SQL语句
        /// </summary>
        public static int ExecuteCommand(string sql, params SQLiteParameter[] values)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        ///  执行带参SQL语句，并返回当前插入的数据的id
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteAndReturnId(string sql, params SQLiteParameter[] values)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return Convert.ToInt32(cmd.ExecuteScalar());
        }
        /// <summary>
        /// 执行无参SQL语句，并返回执行记录数
        /// </summary>
        public static int GetScalar(string safeSql)
        {
            SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行有参SQL语句，并返回执行记录数
        /// </summary>
        public static int GetScalar(string sql, params SQLiteParameter[] values)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 执行无参SQL语句，并返回SqlDataReader
        /// </summary>
        public static SQLiteDataReader GetReader(string safeSql)
        {
            SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
            SQLiteDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 执行有参SQL语句，并返回SqlDataReader
        /// </summary>
        public static SQLiteDataReader GetReader(string sql, params SQLiteParameter[] values)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SQLiteDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static DataSet GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }

        public static DataSet GetDataSet(string sql, params SQLiteParameter[] values)
        {
            DataSet ds = new DataSet();
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }

        public static DataTable GetDataTable(string safeSql)
        {
            DataSet ds = new DataSet();
            SQLiteCommand cmd = new SQLiteCommand(safeSql, Connection);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataTable(string sql, params SQLiteParameter[] values)
        {
            DataSet ds = new DataSet();
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 根据sql语句获得一个单值字符串
        /// </summary>
        public static string ReturnStringScalar(string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
            try
            {
                string result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return "0";
            }
            connection.Close();
        }
        /// <summary>
        /// 执行事务处理SQL
        /// </summary>
        public static bool ExecTSQL(string[] sqls)
        {
            connection.Open();
            SQLiteTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sqls.Length; i++)
                {
                    if (sqls[i] == "" || sqls[i] == null)
                    {
                        continue;
                    }
                    SQLiteCommand cmd = connection.CreateCommand();
                    cmd.Transaction = trans;
                    cmd.CommandText = sqls[i];
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return true;
            }
            catch
            {

                trans.Rollback();
                return false;
            }
            finally
            {
                trans = null;
                connection.Close();
            }
        }
    }
}

